﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetSubscripitionClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetSubscripitionClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetSubscripitionClass]
    @UserName nvarchar(255)
/*

============================================================
功能:    得到@UserName的所有订阅
参数:
    @UserName nvarchar(255)        :    用户名
============================================================

*/
As
BEGIN
SET NOCOUNT ON

DECLARE @Staff_id int

SET @staff_id=0

--取得用户的ID
SELECT @staff_id = staff_id FROM uds_staff WHERE staff_name = @username

SELECT a.classid, a.classname, a.classremark, a.status, b.scale, b.startdate, b.enddate,
        (SELECT count(*)
            FROM
                uds_document
            WHERE
                classid = a.classid
                and docapproved = 1
                and doctype = 0
        ) AS FILECOUNT
    FROM
        uds_class a
        left outer join uds_teaminfo b on a.classid = b.teamid
        inner join uds_subscription c on c.classid = a.classid
    WHERE
        c.staff_id = @staff_id

END
GO